package com.huike.pmps.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.huike.pmps.model.dto.cockpit.admin.NewSignAdminDTO;
import com.huike.pmps.model.dto.cockpit.province.NewSignPieProvinceDTO;
import com.huike.pmps.model.dto.cockpit.province.NewSignProvinceDTO;
import com.huike.pmps.model.dto.cockpit.region.NewSignRegionDTO;
import com.huike.pmps.model.entity.NewSign;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 作者：谌贵斌(黑桃K)
 * 日期: 2888-08-08
 * 老师QQ: 272488352
 * 官方QQ交流群: 377748272
 * 刀客程序员官网：http://www.daoke360.com
 * 刀客程序员淘宝旗舰店：https://daoke360.taobao.com/
 */
public interface NewSignDao extends BaseMapper<NewSign> {

    @Insert({
            "<script>",
            "insert into p_new_sign(           ",
            "  customer_name,                  ",
            "  contract_title,                 ",
            "  contract_amount,                ",
            "  financial_year,                 ",
            "  financial_amount,               ",
            "  charge_name,                    ",
            "  contract_no,                    ",
            "  product_type_id,                ",
            "  product_type_name,              ",
            "  region_id,                      ",
            "  region_name,                    ",
            "  province_id,                    ",
            "  province_name,                  ",
            "  performance_date,               ",
            "  year,                           ",
            "  month,                          ",
            "  week,                           ",
            "  week_of_year,                    ",
            "  season_of_year,                  ",
            "  month_of_year,                   ",
            "  create_time                     ",
            ")values                           ",
            "<foreach collection='list' item='item' index='index' separator=','>",
            "(							",
            " #{item.customerName},     ",
            " #{item.contractTitle},    ",
            " #{item.contractAmount},   ",
            " #{item.financialYear},    ",
            " #{item.financialAmount},  ",
            " #{item.chargeName},       ",
            " #{item.contractNo},       ",
            " #{item.productTypeId},    ",
            " #{item.productTypeName},  ",
            " #{item.regionId},         ",
            " #{item.regionName},       ",
            " #{item.provinceId},       ",
            " #{item.provinceName},     ",
            " #{item.performanceDate},  ",
            " #{item.year},             ",
            " #{item.month},            ",
            " #{item.week},             ",
            " #{item.weekOfYear},       ",
            " #{item.seasonOfYear},     ",
            " #{item.monthOfYear},      ",
            " #{item.createTime}       ",
            ")                          ",
            "</foreach >",
            "</script>",
    })
    int insertBatchList(List<NewSign> list);


    /**
     * 省区-新签和同数量及金额
     */
    @Select({
            "<script>",
            "SELECT								    ",
            "province_name,                         ",
            "COUNT(id)contract_number,              ",
            "SUM(contract_amount)contract_amount    ",
            "FROM p_new_sign                        ",
            "<where>                                ",
            "   <if test='provinceId!=null'>        ",
            "     and  province_id=#{provinceId}    ",
            "   </if>                               ",
            "   <if test='year!=null'>              ",
            "     and  year=#{year}                 ",
            "   </if>                               ",
            "   <if test='month!=null'>             ",
            "     and  month=#{month}               ",
            "   </if>                               ",
            "</where>                               ",
            "GROUP BY province_name                 ",
            "</script>",
    })
    List<NewSignProvinceDTO> selectProvinceNewSignData(Integer provinceId, Integer year,Integer month);



    /**
     * 战区-新签和同数量及金额
     */
    @Select({
            "<script>",
            "SELECT region_name, COUNT(id)contract_number,SUM(contract_amount)contract_amount				",
            "FROM p_new_sign                                                                                ",
            "<where>                                                                                        ",
            "   <if test='regionId!=null'>                                                                  ",
            "     and  region_id=#{regionId}                                                                ",
            "   </if>                                                                                       ",
            "   <if test='year!=null'>                                                                      ",
            "     and  year=#{year}                                                                         ",
            "   </if>                                                                                       ",
            "   <if test='month!=null'>                                                                     ",
            "     and  month=#{month}                                                                       ",
            "   </if>                                                                                       ",
            "</where>                                                                                       ",
            "                                                                                               ",
            "UNION ALL                                                                                      ",
            "                                                                                               ",
            "SELECT province_name region_name, COUNT(id)contract_number,SUM(contract_amount)contract_amount ",
            "FROM p_new_sign                                                                                ",
            "<where>                                                                                        ",
            "   <if test='regionId!=null'>                                                                  ",
            "     and  region_id=#{regionId}                                                                ",
            "   </if>                                                                                       ",
            "   <if test='year!=null'>                                                                      ",
            "     and  year=#{year}                                                                         ",
            "   </if>                                                                                       ",
            "   <if test='month!=null'>                                                                     ",
            "     and  month=#{month}                                                                       ",
            "   </if>                                                                                       ",
            "</where>                                                                                       ",
            "GROUP BY province_name                                                                         ",
            "</script>",
    })
    List<NewSignRegionDTO> selectRegionNewSignData(Integer regionId, Integer year,Integer month);




    /**
     * 管理员-新签和同数量及金额
     */
    @Select({
            "<script>",
            "SELECT '全部' region_name, COUNT(id)contract_number,SUM(contract_amount)contract_amount ",
            "FROM p_new_sign                                                                         ",
            "<where>                                                                                 ",
            "   <if test='year!=null'>                                                               ",
            "     and  year=#{year}                                                                  ",
            "   </if>                                                                                ",
            "   <if test='month!=null'>                                                              ",
            "     and  month=#{month}                                                                ",
            "   </if>                                                                                ",
            "</where>                                                                                ",
            "                                                                                        ",
            "UNION ALL                                                                               ",
            "                                                                                        ",
            "SELECT  region_name, COUNT(id)contract_number,SUM(contract_amount)contract_amount       ",
            "FROM p_new_sign                                                                         ",
            "<where>                                                                                 ",
            "   <if test='year!=null'>                                                               ",
            "     and  year=#{year}                                                                  ",
            "   </if>                                                                                ",
            "   <if test='month!=null'>                                                              ",
            "     and  month=#{month}                                                                ",
            "   </if>                                                                                ",
            "</where>                                                                                ",
            "GROUP BY region_name                                                                    ",
            "</script>",
    })
    List<NewSignAdminDTO> selectAdminNewSignData(Integer year,Integer month);



    @Select({
            "<script>",
            "SELECT province_name region_name, COUNT(id)contract_number,SUM(contract_amount)contract_amount ",
            "FROM p_new_sign                                                                                ",
            "<where>                                                                                 ",
            "   <if test='regionName!=null'>                                                                ",
            "     AND  region_name=#{regionName}                                                            ",
            "   </if>                                                                                       ",
            "   <if test='year!=null'>                                                                      ",
            "     AND  YEAR=#{year}                                                                         ",
            "   </if>                                                                                       ",
            "   <if test='month!=null'>                                                                     ",
            "     AND  MONTH=#{month}                                                                       ",
            "   </if>                                                                                       ",
            "</where>                                                                                ",
            "GROUP BY province_name                                                                         ",
            "</script>",
    })
    List<NewSignAdminDTO>selectAdminNewSignDataChild(String regionName,Integer year,Integer month);


    /**
     * 省-产品销售金额数据
     */
    @Select({
            "<script>",
            "select product_type_name , sum(contract_amount) contract_amount",
            "from p_new_sign                                          ",
            "<where>                                                  ",
            "   <if test='provinceId!=null'>                          ",
            "     and  province_id=#{provinceId}                      ",
            "   </if>                                                 ",
            "   <if test='year!=null'>                                ",
            "     and  year=#{year}                                   ",
            "   </if>                                                 ",
            "   <if test='month!=null'>                               ",
            "     and  month=#{month}                                 ",
            "   </if>                                                 ",
            "</where>                                                 ",
            "group by product_type_name                               ",
            "</script>",
    })
    List<NewSignPieProvinceDTO> selectProvinceProductNewSignContractAmount(Integer provinceId, Integer year, Integer month);



    /**
     * 战区-产品销售金额数据
     */
    @Select({
            "<script>",
            "select product_type_name , sum(contract_amount) contract_amount    ",
            "from p_new_sign                                                    ",
            "<where>                                                            ",
            "   <if test='regionId!=null'>                                      ",
            "     and  region_id=#{regionId}                                    ",
            "   </if>                                                           ",
            "   <if test='provinceName!=null'>                                  ",
            "     and  province_name=#{provinceName}                            ",
            "   </if>                                                           ",
            "   <if test='year!=null'>                                          ",
            "     and  year=#{year}                                             ",
            "   </if>                                                           ",
            "   <if test='month!=null'>                                         ",
            "     and  month=#{month}                                           ",
            "   </if>                                                           ",
            "</where>                                                           ",
            "group by product_type_name                                         ",
            "</script>",
    })
    List<NewSignPieProvinceDTO> selectRegionProductNewSignContractAmount(Integer regionId, String provinceName, Integer year, Integer month);


    /**
     * 战区-产品销售金额数据
     */
    @Select({
            "<script>",
            "select product_type_name , sum(contract_amount) contract_amount    ",
            "from p_new_sign                                                    ",
            "<where>                                                            ",
            "   <if test='regionName!=null'>                                    ",
            "     and  region_name=#{regionName}                                ",
            "   </if>                                                           ",
            "   <if test='provinceName!=null'>                                  ",
            "     and  province_name=#{provinceName}                            ",
            "   </if>                                                           ",
            "   <if test='year!=null'>                                          ",
            "     and  year=#{year}                                             ",
            "   </if>                                                           ",
            "   <if test='month!=null'>                                         ",
            "     and  month=#{month}                                           ",
            "   </if>                                                           ",
            "</where>                                                           ",
            "group by product_type_name                                         ",
            "</script>",
    })
    List<NewSignPieProvinceDTO> selectAdminProductNewSignContractAmount(String regionName,String provinceName, Integer year, Integer month);

}
